﻿--获取公司员工档案
CREATE PROCEDURE [dbo].[proc_Employee_GetCompanyList1]
(
	@startIndex int,
	@endIndex int,
	@docount bit,
	@username nvarchar (50),
	@unitId int,            --单位
	@departmentId int,      --部门
	@grade int,             --职级 
	@state int,             --状态
	@sex nvarchar(4),		--性别
	@name nvarchar(50)	    --姓名
)
as
Begin
	--部门Id
--	;WITH ChildDepartment(ID,DName,PId) AS
--	(
--		SELECT ID,DName,ParentID FROM department WHERE ParentID=@departmentId --固定成员
--	    
--		UNION ALL
--		SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
--		INNER JOIN department ON d.ID = department.ParentID
--	)
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@departmentid

	(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
	select * from ChildDepartment;
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY E.Id DESC)AS Row,
	E.id,E.UserName,E.EName,E.Enumber,E.ETempNumber,E.Sex,E.CompanyId,
    ISNULL((select companyname from company where id=E.CompanyId),'...') as CompanyName,
    E.DepartmentId,
    ISNULL((select dname from Department where id=E.DepartmentId),'...') as DepartmentName,
    E.State,E.PostId,E.GradeId,E.WageNumber,E.RuzhiDate,E.LizhiDate,E.Birthday,
    E.Rtx,E.OfficePhone,E.HomePhone,E.MobilePhone,E.Email,E.IDcard,E.Address,
    E.Education,E.HeadPic,E.UpName,E.UpTime
    From employee E
    WHERE (@unitId=0 or CompanyId=@unitId) and(@departmentId=0 or (DepartmentId in((Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)))) and(@grade=0 or gradeid=@grade) and (@state=0 or State=@state) and(@sex=0 or sex=@sex) and (@name='' Or EName like '%'+@name+'%')
	)
	Select * From list Where Row between @StartIndex and @EndIndex 
End
	RETURN
